package com.rjyb.student_management_platform.dao;

import com.rjyb.student_management_platform.pojo.*;
import com.rjyb.student_management_platform.pojo.Class;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Service;
import org.springframework.ui.Model;

import javax.servlet.http.HttpSession;
import java.util.List;
import java.util.Map;

@Service
public interface TeacherDao {

    //1.查询班级
    @Select("select c.className,c.cid from teacher t ,class_teacher ct,class c where t.tid=#{id} and ct.tid=t.tid and ct.cid=c.cid")
    List<Map> selclass(int id);


    //2.查询学生
    @Select("select * from student s,class_student cs,class_teacher ct where  ct.tid=#{tid} and ct.cid=#{cid} and cs.cid=#{cid} and cs.sid=s.sid ORDER BY s.studentId ASC")
    List<Student> selestudent(int cid,int tid);


    //3.添加老师
    @Update("UPDATE  teacher SET name=#{name},password=#{password},jobNumber=#{jobNumber} WHERE  name=#{name} and jobNumber=#{jobNumber};")
    boolean addTeacher(Teacher teacher);


    //4.注册验证
    @Select("select * from teacher where name=#{name} and jobNumber=#{jobNumber}")
    Teacher ifTeacher(@Param("name") String name, @Param("jobNumber")  String jobNumber);



    //5.老师查本人发布过的作业
    @Select("select * from jobtable j,teacher t where #{tid} = j.tid = t.tid  and cid=#{cid} ORDER BY jobData DESC")
    List<Map> selejob(Long tid, int cid);
    @Select("select * from jobtable j,teacher t where cid=#{cid} and j.tid =t.tid ORDER BY jobData DESC")
    List<Map> selejob1(int cid);

    //6.应交人数
    @Select("select count(*) from class c,class_student cs where c.cid=#{cid} and c.cid=cs.cid")
    int selecpnum(int cid);

    //7.已交人数
    @Select("select count(*) from answer_sheet where jid=#{jid}")
    int selecpnum1(int jid);


    //8.已批人数
    @Select("select count(*) from answer_sheet where jid=#{jid} and correct=2")
    int selecpnum2(int jid);

    //9.未批人数
    @Select("select count(*) from answer_sheet where jid=#{jid} and correct=1")
    int selecpnum3(int jid);


    //10.根据作业ID查询作业
    @Select("select sid from answer_sheet ans where ans.jid=#{jid}")
    List<AnswerSheet> selectclass(int jid);

    //11.查看当前作业未交学生
    @Select("<script>" +
            "select s.studentId, s.name from student s,class_student cs where s.sid not in" +
            "<foreach item='selectclass' collection='list' open='('  close=')' separator=','>" +
            "${selectclass.sid}" +
            "</foreach>" +
            "and cs.cid=#{cid} and s.sid=cs.sid ORDER BY s.studentId ASC" +
            "</script>")
    List<Student> selectclass1(List list, int cid);


    //12.班级查询学生
    @Select("select s.studentId,s.name from class_student cs,student s where cs.cid=#{cid} and cs.sid = s.sid ORDER BY s.studentId ASC ")
    List<Student> selectclass2(int cid);


    //13.查询某个老师的班级
    @Select("select * from class_teacher ct, class c where #{id} = ct.tid and ct.cid=c.cid;")
    List<Class> selectClass(long id);

    //14.按照班级查询学生
    @Select("select * from class_student cs,student t,class c where #{cid} = cs.cid=c.cid and cs.sid = t.sid order by t.studentId")
    List<Map> selectStu(Class c);

    //15.添加考勤
    @Insert("<script>"+
            "insert into attendance values"+
            "<foreach collection='mapList' item='mapList' separator=','>"+
            "(null,#{tid},#{cid},#{mapList.sid},#{atDate},#{mapList.atRate},#{classSection})"+
            "</foreach>"+
            "</script>")
    void attendanceAdd(@Param("mapList") List<Map<String, Long>> mapList, @Param("cid") Long cid,@Param("classSection") Long classSection,@Param("atDate") String adDate,@Param("tid") long tid);

    //16.查询是否添加过今天的这节课的考勤
    @Select("select * from attendance where class_section = #{classSection} and atDate = #{adDate} and cid = #{cid}")
    List<Attendance> selectAtt(@Param("classSection") Long classSection,@Param("adDate") String adDate,@Param("cid") long cid);

    //17.老师id查询班级
    @Select("select * from class_teacher ct,class c where ct.tid=#{tid} and ct.cid=c.cid")
    List<Class> toCheckattendance(long tid);

    //18.老师查询考勤
    @Select("select * from attendance att,student s where att.atDate=#{time} and att.sid=s.sid and att.cid=#{cid} and att.class_section=#{class_section} ORDER BY att.atDate ASC")
    List<Map> selectattendance(@Param("cid") long cid,@Param("time") String time,@Param("class_section") long class_section);

    //19.班导的查询考勤
    @Select("select * from attendance att,student s where att.atDate=#{time} and att.sid=s.sid and att.cid=#{cid} and att.class_section=#{class_section} and att.tid=#{tid} ORDER BY att.atDate ASC")
    List<Map> selectattendance1(@Param("cid") long cid,@Param("time") String time,@Param("class_section") long class_section,@Param("tid") long tid);


    //20.老师name
    @Select("select t.name from attendance att,student s,teacher t where att.atDate=#{time} and att.sid=s.sid and att.cid=#{cid} and att.class_section=#{class_section} and t.tid=att.tid ORDER BY att.atDate ASC")
    List<Map> tname(@Param("cid") long cid,@Param("time") String time,@Param("class_section") long class_section);

    //21.修改考勤
    @Update("update attendance set atRate = #{atRate} where cid = #{cid} and sid = #{sid} and atDate = #{atDate} and  class_section = #{class_section}")
    void attendanceModify(@Param("class_section") Long class_section, @Param("sid")Long sid, @Param("atDate")String atDate, @Param("cid")Long cid, @Param("atRate")Long atRate);

    //22.按周查看考勤
    @Select("select distinct ((select count(*) from attendance a1 where a1.tid=a.tid and atRate=1 and a1.atDate=a.atDate)/(select count(*) from attendance a2 where a2.tid=a.tid and a2.atDate=a.atDate))*100 as dkl from attendance a where a.tid=#{tid} and a.atDate=#{s}")
    Integer selectAttendance(@Param("tid") long tid,@Param("s") String s);

    //23.查看作业的完成率 查看班级
    @Select("SELECT j.jid ,j.jobname,j.jobData,b.subjectname,((SELECT COUNT(*) FROM answer_sheet an WHERE an.jid=j.jid)/(SELECT COUNT(*) FROM class_student cs WHERE cs.cid=j.cid))*100  AS wcl FROM jobtable j,booksubject b  WHERE j.tid=#{tid} AND j.bid=b.bid ")
    List<Map> selectCompletionRate(long tid);

    //24.查看本天课程考勤
    @Select("select distinct ((select count(*) from attendance a1 where a1.tid=a.tid and atRate=1 and a1.atDate=a.atDate and a1.class_section=a.class_section)/(select count(*) from attendance a2 where a2.tid=a.tid and a2.atDate=a.atDate and a2.class_section=a.class_section))*100 as dkl  from attendance a where a.tid=#{tid} and a.atDate=#{at} and a.class_section=#{cs}")
    Integer selectAttendanceForTheDay(@Param("tid") long tid,@Param("at") String at,@Param("cs") long cs);


    //查询已完成作业类型
    @Select("select an.*,s.name,s.studentId from answer_sheet an,jobtable j,student s where s.sid=an.sid and j.jid=an.jid and j.jid=#{jid} and cid=#{cid} and remedy = 2 ORDER BY s.studentId ASC")
    List<Map> selectjob1(int jid, int cid);
    //查询已完成作业学生
    @Select("select an.*,s.name,s.studentId from answer_sheet an,jobtable j,student s where s.sid=an.sid and j.jid=an.jid and j.jid=#{jid} and cid=#{cid} and remedy=1 and  correct = 2 ORDER BY s.studentId ASC")
    List<Map> selectjob2(int jid, int cid);

    //班导查看本周考勤
    @Select("select distinct ((select count(*) from attendance a2 where a2.cid=a.cid and a2.atDate=a.atDate and a2.atRate=1)/(select count(*) from attendance a3 where a3.cid=a.cid and a3.atDate=a.atDate))*100 as dkl from teacher t,class_teacher ct ,class c,class_student cs,attendance a where t.tid=#{tid} and a.atDate =#{s} and t.tid=ct.tid and ct.cid=c.cid and c.cid=cs.cid and a.sid=cs.sid")
    Integer selectClassGuideAttendance(@Param("tid") long tid,@Param("s") String s);

    //班导查询作业完成率 查看班级-
    @Select("select j.jobname,j.jobData,b.subjectname,((select count(*) from answer_sheet a where  j.jid=a.jid)/(select count(*)from class_student cs where cs.cid=j.cid))*100 as wcl from class_teacher ct ,class c,jobtable j,booksubject b where ct.tid=#{tid} and ct.cid=c.cid and j.cid=c.cid and  j.bid=b.bid")
    List<Map> selectClassGuideCompletionRate(long tid);

    //班导查看本天课程考勤
    @Select("select distinct ((select count(*) from attendance a2 where a2.cid=a.cid and a2.atDate=a.atDate and  a2.class_section=a.class_section and a2.atRate=1)/(select count(*) from attendance a3 where a3.cid=a.cid and  a3.class_section=a.class_section and a3.atDate=a.atDate))*100 as dkl from teacher t,class_teacher ct ,class c,class_student cs,attendance a where t.tid=#{tid} and a.atDate =#{at} and a.class_section=#{cs} and t.tid=ct.tid and ct.cid=c.cid and c.cid=cs.cid and a.sid=cs.sid")
    Integer selectClassGuideAttendanceForTheDay(@Param("tid") long tid,@Param("at") String at,@Param("cs") long cs);

    //班导查询本班本周已到人数
    @Select("select count(*) from teacher t,class_teacher ct ,class c,class_student cs,attendance a where t.tid=#{tid} and a.atDate =#{adDate} and t.tid=ct.tid and ct.cid=c.cid and c.cid=cs.cid and a.sid=cs.sid and atRate=1 and class_section=#{class_section}")
    Integer selectAttendanceHasArrived(@Param("tid") long tid,@Param("adDate") String adDate,@Param("class_section") int class_section);

    //班导查询本班人数
    @Select("select count(*) from class_teacher ct ,class_student cs where ct.tid=#{tid} and ct.cid=cs.cid")
    Integer selectPeople(@Param("tid") long tid,@Param("atDate") String atDate);
}
